﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
using CommClass;
using DMS;


namespace DMS

{
    public partial class FrmDoImport : DMS.FrmTemplate
    {
        public FrmDoImport()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }
        string excelfilename;
        //数据库连接语句
        SqlConnection conn = new SqlConnection("server=10.228.20.1;database=DMS;uid=sa;pwd=Admin0503");



        private void button1_Click(object sender, EventArgs e)
        {
            Stream myStream;
            OpenFileDialog openFileDialog1 = new OpenFileDialog();

            openFileDialog1.InitialDirectory = "h:\\";
            openFileDialog1.Filter = "Excel files (*.xls;*.xlsx)|*.xls;*.xlsx";
            openFileDialog1.FilterIndex = 2;
            openFileDialog1.RestoreDirectory = true;

            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                if ((myStream = openFileDialog1.OpenFile()) != null)
                {
                    // Insert code to read the stream here.
                    myStream.Close();
                }
            }
            excelfilename = openFileDialog1.FileName.ToString();
            textBox1.Text = openFileDialog1.FileName.ToString();


        }

        //从excel获取数据
        public DataTable getExcelData()
        {

            string Constr = "Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=" + excelfilename + ";Extended Properties=\"Excel 12.0;hdr=yes\"";
            string sqlstr = "select * from [sheet2$] order by 提单号码 desc ";
            OleDbConnection Con = new OleDbConnection(Constr);
            DataSet ds;
            OleDbDataAdapter OleDat = new OleDbDataAdapter(sqlstr, Con);
            ds = new DataSet();
            OleDat.Fill(ds);
            return ds.Tables[0];

        }





        private void button2_Click(object sender, EventArgs e)
        {
            //显示excel数据

            dataGridView1.DataSource = getExcelData();
            this.button3.Enabled = true;


        }

        //用于匹配承运商编号
        public DataTable checkCarrierDate(string carrierdesc)
        {

            string sqlstr = "select carrierid,shortname from t_carrier where shortname='" + carrierdesc + "'";

            SqlDataAdapter da = new SqlDataAdapter(sqlstr, conn);
            DataSet dst = new DataSet();
            da.Fill(dst);

            return dst.Tables[0];
        }
        //用于匹配产品编号
        public DataTable checkProdId(string prodcode)
        {
            string sqlstr = "select prodid,prodcode from t_product where prodcode='" + prodcode + "'";
            SqlDataAdapter da = new SqlDataAdapter(sqlstr, conn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }

        //用于检查提单是否重复
        public DataTable checkDoNumber(string donumber)
        {
            string sqlstr = "select deliverynumber from t_do where deliverynumber ='" + donumber + "'";
            SqlDataAdapter da = new SqlDataAdapter(sqlstr, conn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }

        //检查EXCEL数据是否符合导入要求
        public string checkExcelData(DataTable dt)
        {
            string donumber="";
            DataRow[] dr;
            DataTable dw;
            DataTable dp;
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                dw = checkCarrierDate(dt.Rows[j][32].ToString());
                dp = checkProdId(dt.Rows[j][20].ToString());
                dr = dt.Select("物料代码='" + dt.Rows[j][20].ToString() + "'");
                DataTable dpt = new DataTable();
                dpt = dr.CopyToDataTable();



                //承运商编号和产品编号都能匹配到
                if (dw.Rows.Count > 0 && dp.Rows.Count > 0)
                {
                    //判断源数据提单和产品是否重复
                    if (dpt.Rows.Count > 1)
                    {
                        listBox1.Items.Add(dt.Rows[j][1] + "该提单源数据重复被忽略。");
                        donumber = dt.Rows[0][1].ToString ();
                        break;
                    }
                    else
                    {
                        DataTable dd = checkDoNumber(dt.Rows[0][1].ToString());

                        //判断提单是否已存在
                        if (dd.Rows.Count > 0)
                        {

                            listBox1.Items.Add(dt.Rows[0][1] + "该提单系统中已存在被忽略。");
                            donumber = dt.Rows[0][1].ToString();
                            break;

                        }

                    }

                }
                else
                {
                    listBox1.Items.Add("提单:" + dt.Rows[j][1] + ",承运商:" + dt.Rows[j][32] + ",产品代码:" + dt.Rows[j][20] + "与系统不符，请核实。");
                    donumber = dt.Rows[0][1].ToString();
                    continue;

                }

            }
            return donumber;

        }

      

        private void button3_Click(object sender, EventArgs e)
        {
            listBox1.Items.Add("开始导入数据...");
            //获取excel数据
            DataTable dtt = getExcelData();

            conn.Open();

            //遍历excel数据并上传至数据库
            int i = 1;
            
            while (dtt.Rows[i][0].ToString() != "")
            {
               
                DataRow[] dr;
                DataTable dt;
                DataTable dw;
                DataTable dp;
                string deliverynumber;
                                
                if (dtt.Rows[i][1].ToString() != dtt.Rows[i + 1][1].ToString())
                {
                    dr = dtt.Select("提单号码='" + dtt.Rows[i][1].ToString() + "'");
                    dt = dr.CopyToDataTable();
                    deliverynumber = checkExcelData(dt);
                    if (deliverynumber == "")
                    {
                        dw = checkCarrierDate(dt.Rows[0][32].ToString());
                         string sqlstr = "insert into t_do (deliverynumber,ordernumber,ordertype,saletype,ponumber,orderdatetime," +
                          " instocktime,soldto,deliveryto,custname,deliveryaddress,carrierid,vehiclenumber,remark) values ('" +
                           dt.Rows[0][1] + "','" + dt.Rows[0][0] + "','" + dt.Rows[0][3] + "','" + dt.Rows[0][4] + "','" + dt.Rows[0][6] +
                           "','" + dt.Rows[0][8] + "','" + dt.Rows[0][10] + "','" + dt.Rows[0][11] + "','" + dt.Rows[0][12] +
                           "','" + dt.Rows[0][13] + "','" + dt.Rows[0][14] + "','" + dw.Rows[0][0] + "','" + dt.Rows[0][33] + "','" + dt.Rows[0][34] + "')";
                         SqlCommand cmd = new SqlCommand(sqlstr, conn);
                         cmd.ExecuteNonQuery();

                        for (int j = 0; j < dt.Rows.Count; j++)
                        {
                            dp = checkProdId(dt.Rows[j][20].ToString());
                            string sql = " insert into t_doentry (deliverynumber,prodid,orderqty,unitweight) values ('" + dt.Rows[j][1] + "','" + dp.Rows[0][0] + "','" + dt.Rows[j][22] + "','" + dt.Rows[j][26] + "')";

                            SqlCommand cmmd = new SqlCommand(sql, conn);
                            cmmd.ExecuteNonQuery();


                        }


                    }

                 }
                                         
                i++;
               
            }

            listBox1.Items.Add("数据导入完成！");
            conn.Close();

        }
    }
}
